﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class ColorDAL : IColorDAL
    {
        #region SQL
        public const string sqlGetColor = "SELECT * FROM TbColor";
        const string sqlUpdateColor = "UPDATE TbColor SET ColorName = {0},Sort = {1} WHERE (ColorID = {2})";
        const string sqlInsertColor = "INSERT INTO TbColor (ColorName, Sort) VALUES ({0},{1})";
        const string sqlDeleteColor = "Delete from TbColor where ColorID={0}";
        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetColor()
        {
            return DAO.Select(sqlGetColor);
        }

        public bool UpdateColor(Model.Color color)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ColorName", color.ColorName),
                new SqlParameter ("@ColorID", color.ColorID)
            };
            string sql = string.Format(sqlUpdateColor, "@ColorName", "@ColorID");

            return DAO.ExecuteSql(sql, sp);
        }

        public bool UpdateColors(List<Model.Color> colors)
        {
            Dictionary<SqlParameter[], string> updateSqls = new Dictionary<SqlParameter[], string>();
            for (int i = 0; i < colors.Count; i++)
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter ("@ColorName",colors[i].ColorName),
                    new SqlParameter ("@Sort",colors[i].Sort),
                    new SqlParameter ("@ColorID",colors[i].ColorID)
                  };
                string sql = string.Format(sqlUpdateColor, "@ColorName", "@Sort", "@ColorID");
                updateSqls.Add(parms, sql);
            }
            return DAO.ExecuteSqlTran(updateSqls);
        }

        public bool InsertColor(Model.Color color)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ColorName", color.ColorName),
                new SqlParameter ("@Sort", color.Sort)
            };
            string sql = string.Format(sqlInsertColor, "@ColorName", "@Sort");

            return DAO.ExecuteSql(sql, sp);
        }

        public bool DeleteColor(int colorID)
        {
            SqlParameter[] sp = new SqlParameter[] { 
                new SqlParameter ("@ColorID",colorID),
            };
            string sql = string.Format(sqlDeleteColor, "@ColorID");
            return DAO.ExecuteSql(sql, sp);
        }
    }
}
